﻿Imports ICSharpCode.SharpZipLib.Zip
Imports System.IO
Imports TheMovieDb
Imports System.Net
Imports System.Collections.ObjectModel
Imports System.Text




Module Module1
    Dim workDir As String = My.Settings.WorkDir
    Dim MOVLIB As New MOVLIBDataContext(My.Settings.MOVLIBConnectionString)
    Sub Main()
        'Exit Sub
        If NewFile() Then
            CleanUpWorkDir()
            CleanUpTables()
            DownloadFiles()
            UnzipFiles()
            ProcessDVDFile()
            ProcessPre()
            ProcessActorsIndex()
            ProcessActorsName()
            ProcessDirectorsIndex()
            ProcessDirectorsName()
            CleanUpWorkDir()
        Else
            Update("No new files exist - exiting routine")
        End If
        Update("Entering Poster Download")
        DownloadPosters()
    End Sub

    Private Function NewFile() As Boolean
        'startspan -->Monday, November 07, 2011 11:13:13 AM<
        Dim mov As New MOVLIBDataContext(My.Settings.MOVLIBConnectionString)

        Dim lastTime As DateTime
        Try
            lastTime = (From dt In mov.LastImportTimes Select dt.LastImportTime).Max
            Update("Last Import Time was: " & lastTime)

        Catch ex As Exception
            Update("No record of last import time")
            lastTime = Date.MinValue
        End Try

        Dim wc As New WebClient()
        Dim source = wc.DownloadString(My.Settings.wwwTime)
        Dim start As Integer = source.IndexOf("startspan -->") + 13
        Dim temp As String = source.Substring(start, 50)

        Try
            Dim test As DateTime = CDate(temp.Split("<")(0))
            Update("Most recent file is: " & test)
            If test > lastTime Then

                Dim importTime As New MOVLIB_Batch.LastImportTime With {.LastImportTime = test}


                mov.LastImportTimes.InsertOnSubmit(importTime)
                mov.SubmitChanges()

                Return True

            Else

                Return False
            End If
        Catch ex As Exception
            Red("Error parsing date: " & ex.Message)
            Return False
        End Try

        Return False
    End Function
    Private Sub CleanUpWorkDir()
        Update("Cleaning up work directory")
        With My.Computer.FileSystem
            For Each f As String In .GetFiles(workDir)
                Try
                    .DeleteFile(f)
                Catch ex As Exception
                    Red(String.Format("Unable to delete {0} - {1}", .GetName(f), ex.Message))
                End Try

            Next
        End With
    End Sub
    Private Sub CleanUpTables()

        Update("Clearing tables")


        Try
            'Dim sql As New DBHelper.SQLHelper(My.Settings.MOVLIBConnectionString)
            'sql.CommandTimeOut = My.Settings.TimeOut
            'sql.SQLCommand = "cleartables "
            'sql.ExecuteNonQuery()
            MOVLIB.ExecuteCommand("cleartables")
        Catch ex As Exception
            Red(String.Format("Error clearing tables {0}", ex.Message))
        End Try


    End Sub
    Private Sub Red(ByVal msg As String)
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("{0:T} - {1}", Now, msg)
        Console.ForegroundColor = ConsoleColor.Gray
    End Sub

    Private Sub Update(ByVal msg As String)
        Console.WriteLine("{0:T} - {1}", Now, msg)
    End Sub

    Private Sub ProcessDVDFile()
        Update("Reading DVD File")
        Dim dataTable As DataTable = CreateDataTable("DVD")

        Dim ParseFields() As String = {","}
        Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_DVD), ParseFields)

        With input
            .TextFieldType = FileIO.FieldType.Delimited
            .HasFieldsEnclosedInQuotes = True
            .TrimWhiteSpace = True
        End With

        Dim linecount As Integer = 0
        Dim row As DataRow
        Dim fields() As String = {}
        Do Until input.EndOfData
            linecount += 1
            Try
                fields = input.ReadFields
                If fields.Count > 0 And linecount > 1 Then
                    row = dataTable.NewRow()
                    row("ID") = fields(13)
                    row("DVD_Title") = fields(0)


                    Try
                        row("Released") = CDate(fields(2))
                    Catch ex As Exception
                    End Try

                    row("Status") = fields(3)
                    row("Rating") = fields(7)
                    row("UPC") = fields(11)
                    Try
                        row("DVD_ReleaseDate") = CDate(fields(12))
                    Catch ex As Exception
                    End Try

                    row("Timestamp") = fields(14)

                    dataTable.Rows.Add(row)

                End If


            Catch ex As Exception
                Red(linecount & " - " & ex.Message)


            End Try
        Loop
        CopyTable(dataTable, "DVD")
        dataTable.Dispose()
        input.Close()
    End Sub
    Private Sub ProcessPre()
        Update("Reading Pre File")
        Dim dataTable As DataTable = CreateDataTable("DVD")
        Dim mov As New MOVLIBDataContext(My.Settings.MOVLIBConnectionString)
        Dim maxID As Integer = (From m In mov.DVDs Select m.ID).Max
        Update(maxID)
        Dim ParseFields() As String = {","}
        Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_PRE), ParseFields)

        With input
            .TextFieldType = FileIO.FieldType.Delimited
            .HasFieldsEnclosedInQuotes = True
            .TrimWhiteSpace = True
        End With

        Dim linecount As Integer = 0
        Dim row As DataRow
        Dim fields() As String = {}
        Do Until input.EndOfData
            linecount += 1
            Try
                fields = input.ReadFields
                If fields.Count > 0 And linecount > 1 Then
                    row = dataTable.NewRow()
                    row("ID") = maxID + 1
                    maxID += 1
                    row("DVD_Title") = fields(0)


                    Try
                        row("Released") = CDate(fields(2))
                    Catch ex As Exception
                    End Try

                    'row("Status") = fields(3)
                    'row("Rating") = fields(7)
                    row("UPC") = fields(6)
                    Try
                        row("DVD_ReleaseDate") = CDate(fields(2))
                    Catch ex As Exception
                    End Try

                    row("Timestamp") = fields(10)

                    dataTable.Rows.Add(row)

                End If


            Catch ex As Exception

                Red(linecount & " - " & ex.Message)

            End Try
        Loop
        CopyTable(dataTable, "DVD")
        dataTable.Dispose()
        input.Close()
    End Sub

    Private Sub ProcessActorsIndex()
        Update("ProcessActorsIndex")
        Dim dataTable As DataTable = CreateDataTable("Actors_Index")
        Dim ParseFields() As String = {","}
        Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_ActorsIndex), ParseFields)

        With input
            .TextFieldType = FileIO.FieldType.Delimited
            .HasFieldsEnclosedInQuotes = False
            .TrimWhiteSpace = True
        End With
        Dim linecount As Integer = 0
        Dim row As DataRow
        Dim fields() As String = {}
        Do Until input.EndOfData
            linecount += 1
            Try
                fields = input.ReadFields
                If fields.Count > 0 And linecount > 1 Then
                    row = dataTable.NewRow()
                    row("ID") = fields(0)
                    row("Actor_ID") = fields(1)
                    row("DVDList_id") = fields(2)

                    dataTable.Rows.Add(row)

                End If


            Catch ex As Exception

                Red(linecount & " - " & ex.Message)

            End Try
        Loop
        CopyTable(dataTable, "Actors_Index")
        dataTable.Dispose()
        input.Close()
    End Sub

    Private Sub ProcessActorsName()
        Update("ProcessActorsName")
        Dim dataTable As DataTable = CreateDataTable("Actors_Names")
        Dim ParseFields() As String = {","}
        Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_ActorsName), ParseFields)

        With input
            .TextFieldType = FileIO.FieldType.Delimited
            .HasFieldsEnclosedInQuotes = True
            .TrimWhiteSpace = True
        End With
        Dim linecount As Integer = 0
        Dim row As DataRow
        Dim fields() As String = {}
        Do Until input.EndOfData
            linecount += 1
            Try
                fields = input.ReadFields
                If fields.Count > 0 And linecount > 1 Then
                    row = dataTable.NewRow()
                    row("Actor_ID") = fields(0)
                    row("Actor") = fields(1)


                    dataTable.Rows.Add(row)

                End If


            Catch ex As Exception

                Red(linecount & " - " & ex.Message)

            End Try
        Loop
        CopyTable(dataTable, "Actors_Names")
        dataTable.Dispose()
        input.Close()
    End Sub

    Private Sub ProcessDirectorsIndex()
        Try
            Update("ProcessDirectorsIndex")
            Dim dataTable As DataTable = CreateDataTable("Directors_Index")
            Dim ParseFields() As String = {","}
            Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_DirectorsIndex), ParseFields)

            With input
                .TextFieldType = FileIO.FieldType.Delimited
                .HasFieldsEnclosedInQuotes = False
                .TrimWhiteSpace = True
            End With
            Dim linecount As Integer = 0
            Dim row As DataRow
            Dim fields() As String = {}
            Do Until input.EndOfData
                linecount += 1
                Try
                    fields = input.ReadFields
                    If fields.Count > 0 And linecount > 1 Then
                        row = dataTable.NewRow()
                        row("ID") = fields(0)
                        row("Director_ID") = fields(1)
                        row("DVDList_ID") = fields(2)


                        dataTable.Rows.Add(row)

                    End If


                Catch ex As Exception



                    Red(linecount & " - " & ex.Message)

                End Try
            Loop
            CopyTable(dataTable, "Directors_Index")
            dataTable.Dispose()
            input.Close()
        Catch ex As Exception
            Red(String.Format("Error Processing Director Index - {0}", ex.Message))

        End Try
    End Sub

    Private Sub ProcessDirectorsName()
        Try
            Update("ProcessDirectorsName")
            Dim dataTable As DataTable = CreateDataTable("Directors_Names")
            Dim ParseFields() As String = {","}
            Dim input As FileIO.TextFieldParser = My.Computer.FileSystem.OpenTextFieldParser(My.Computer.FileSystem.CombinePath(workDir, My.Settings.csv_DirectorsName), ParseFields)

            With input
                .TextFieldType = FileIO.FieldType.Delimited
                .HasFieldsEnclosedInQuotes = True
                .TrimWhiteSpace = True
            End With
            Dim linecount As Integer = 0
            Dim row As DataRow
            Dim fields() As String = {}
            Do Until input.EndOfData
                linecount += 1
                Try
                    fields = input.ReadFields
                    If fields.Count > 0 And linecount > 1 Then
                        row = dataTable.NewRow()
                        row("Director_ID") = fields(0)
                        row("Director") = fields(1)


                        dataTable.Rows.Add(row)

                    End If


                Catch ex As Exception

                    Red(linecount & " - " & ex.Message)

                End Try
            Loop
            CopyTable(dataTable, "Directors_Names")
            dataTable.Dispose()
            input.Close()

        Catch ex As Exception
            Red(String.Format("Error Processing Director Names - {0}", ex.Message))
        End Try
        
    End Sub
    Private Function CreateDataTable(ByVal TableName As String) As DataTable
        Dim x As XElement
        Select Case TableName
            Case "DVD"
                x = <DataTable>
                        <DataColumn><Name>ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>DVD_Title</Name><Type>System.String</Type></DataColumn>
                        <DataColumn><Name>Released</Name><Type>System.DateTime</Type></DataColumn>
                        <DataColumn><Name>Status</Name><Type>System.String</Type></DataColumn>
                        <DataColumn><Name>Rating</Name><Type>System.String</Type></DataColumn>
                        <DataColumn><Name>UPC</Name><Type>System.String</Type></DataColumn>
                        <DataColumn><Name>DVD_ReleaseDate</Name><Type>System.DateTime</Type></DataColumn>
                        <DataColumn><Name>Timestamp</Name><Type>System.DateTime</Type></DataColumn>
                    </DataTable>

                Return DBHelper.DataTableHelper.CreateDataTable(x)
            Case "Actors_Index"
                x = <DataTable>
                        <DataColumn><Name>ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>Actor_ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>DVDList_id</Name><Type>System.Int32</Type></DataColumn>
                    </DataTable>
                Return DBHelper.DataTableHelper.CreateDataTable(x)

            Case "Actors_Names"
                x = <DataTable>
                        <DataColumn><Name>Actor_ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>Actor</Name><Type>System.String</Type></DataColumn>
                    </DataTable>
                Return DBHelper.DataTableHelper.CreateDataTable(x)

            Case "Directors_Index"
                x = <DataTable>
                        <DataColumn><Name>ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>Director_ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>DVDList_ID</Name><Type>System.Int32</Type></DataColumn>
                    </DataTable>
                Return DBHelper.DataTableHelper.CreateDataTable(x)

            Case "Directors_Names"
                x = <DataTable>
                        <DataColumn><Name>Director_ID</Name><Type>System.Int32</Type></DataColumn>
                        <DataColumn><Name>Director</Name><Type>System.String</Type></DataColumn>
                    </DataTable>
                Return DBHelper.DataTableHelper.CreateDataTable(x)

            Case Else

        End Select

        Return New DataTable()


    End Function

    Private Function CopyTable(ByVal myDataTable As DataTable, ByVal tableName As String) As Boolean
        Update(String.Format("Copying {0} records to table: {1}", myDataTable.Rows.Count, tableName))
        Using bulkCopy As System.Data.SqlClient.SqlBulkCopy = New System.Data.SqlClient.SqlBulkCopy(My.Settings.MOVLIBConnectionString)
            If myDataTable.Rows.Count > 0 Then
                bulkCopy.BulkCopyTimeout = (10000)
                bulkCopy.BatchSize = 10000

                bulkCopy.DestinationTableName = tableName

                bulkCopy.WriteToServer(myDataTable)
            End If
        End Using
        Return True

    End Function
    Private Sub DownloadFiles()
        Update("Downloading zip files from website")
        Dim wc As New WebClient()

        Try
            With My.Computer.FileSystem
                Update("Getting actors")
                wc.DownloadFile(My.Settings.wwwActors, .CombinePath(workDir, "actors.zip"))
                Update("Getting directors")
                wc.DownloadFile(My.Settings.wwwDirectors, .CombinePath(workDir, "directors.zip"))
                Update("Getting dvds")
                wc.DownloadFile(My.Settings.wwwDVD, .CombinePath(workDir, "dvd.zip"))
                Update("Getting pre")
                wc.DownloadFile(My.Settings.wwwPre, .CombinePath(workDir, "pre.zip"))
            End With
        Catch ex As Exception
            Red(String.Format("Error downloading files - {0}", ex.Message))
        End Try
    End Sub

    Private Sub UnzipFiles()
        Update("Unzipping Files")
        Try
            Dim zip As New FastZip()
            Dim zipFiles As ReadOnlyCollection(Of String) = My.Computer.FileSystem.GetFiles(workDir, FileIO.SearchOption.SearchTopLevelOnly, {"*.zip"})
            For Each f As String In zipFiles
                Try
                    Update("Unzipping - " & My.Computer.FileSystem.GetName(f))
                    zip.ExtractZip(f, My.Computer.FileSystem.GetParentPath(f), "")
                Catch ex As Exception
                    Red("Error unzipping - " & My.Computer.FileSystem.GetName(f) & " - " & ex.Message)
                End Try
                
            Next
        Catch ex As Exception
            Red(String.Format("Error unzipping files - {0}", ex.Message))
        End Try
       
    End Sub


    Private Sub DownloadPosters()
        Dim test As New TmdbApi(My.Settings.API_KEY)
        Dim wc As New WebClient()
        Dim movies As IEnumerable(Of TmdbMovie) = Nothing
        Dim movlib As New MOVLIBDataContext(My.Settings.MOVLIBConnectionString)
        Dim upc As String = String.Empty

        Dim baseDir As String = "C:\Develop\E\MOVLIB\IMAGES\"

        'With My.Computer.FileSystem
        '    For Each f As String In .GetFiles(baseDir)
        '        .DeleteFile(f)
        '    Next
        'End With


        Do While True
            Console.Write("Please scan code: ")

            Try
                upc = Console.ReadLine()
            Catch ex As Exception
                Red(ex.Message)
            End Try

            If upc.Trim = String.Empty Then Exit Sub



            Dim tmdbUPC = (From u In movlib.Tmdb_Movies Where u.UPC = upc).FirstOrDefault

            If tmdbUPC IsNot Nothing Then
                'Do something with cached stuff
                Update("Already cached info for " & tmdbUPC.Name)
            Else




                Dim title As String = String.Empty
                Dim go As Boolean = True
                Try
                    title = (From t In movlib.DVDs Where t.UPC = upc Select t.DVD_Title).FirstOrDefault
                    If title Is Nothing Then
                        Red("UPC not found!")
                        Console.Write("Enter Movie Title: ")
                        'go = False
                        title = Console.ReadLine()
                    Else
                        If title.Contains("(") Then
                            title = (Left(title, InStr(title, "(") - 2))
                        End If
                        go = True
                    End If
                Catch ex As Exception
                    Red(ex.Message)
                    go = False
                End Try


                If go Then
                    Try
                        movies = Nothing
                        Try

                            movies = test.MovieSearch(title.Replace(":", ""))
                        Catch ex As Exception
                            Red(title & " - FAILED")
                            Dim temp As String() = title.Split(" ")
                            If temp.Count >= 2 Then
                                Console.WriteLine("Trying: " & temp(0) & " " & temp(1))
                                movies = test.MovieSearch(temp(0) & " " & temp(1))
                            End If
                            'Red(ex.Message)
                        End Try

                        If movies IsNot Nothing Then
                            Try
                                Dim movie As TmdbMovie = Nothing
                                If movies.Count > 1 Then
                                    Red("Multiple entries found - please select from the following: ")
                                    Dim i As Integer = 0
                                    For Each m As TmdbMovie In movies
                                        Dim yr As String
                                        If IsDate(m.Released) Then
                                            yr = String.Format(" - ({0})", Year(m.Released))
                                        Else
                                            yr = ""
                                        End If
                                        Console.WriteLine(i & " - " & m.Name & yr)
                                        i += 1
                                    Next

                                    Console.Write("Enter selection: ")
                                    Dim sel As String = Console.ReadLine()
                                    If IsNumeric(sel) Then
                                        If CInt(sel) < movies.Count Then
                                            movie = movies(sel)
                                        End If
                                    Else

                                    End If
                                Else
                                    movie = movies(0)
                                End If


                                If movie IsNot Nothing Then

                                    Dim t_Movie As New Tmdb_Movie()
                                    Dim _url As String = (From u In movie.Posters Where u.ImageInfo.Size = "w342" Select u.ImageInfo.Url).FirstOrDefault
                                    Dim e As Encoding = Encoding.Default
                                    Dim bytes As String = wc.DownloadString(_url)

                                    With t_Movie
                                        .Id = movie.Id
                                        .ImdbId = movie.ImdbId
                                        .Name = movie.Name
                                        .OriginalName = movie.OriginalName
                                        .Overview = movie.Overview
                                        .Rating = movie.Rating
                                        .Released = movie.Released
                                        .Revenue = movie.Revenue
                                        .Runtime = movie.Runtime
                                        .Status = movie.Status
                                        .Tagline = movie.Tagline
                                        .UPC = upc
                                        .URL = movie.Url
                                        .Poster = e.GetBytes(bytes)
                                    End With

                                    movlib.Tmdb_Movies.InsertOnSubmit(t_Movie)

                                    Try
                                        movlib.SubmitChanges()

                                    Catch ex As Exception
                                        Red(ex.Message)
                                    End Try

                                    'Environment.Exit(1)


                                    'thumb
                                    ''w154
                                    'cover
                                    'w342
                                    'mid
                                    'original
                                    'Dim pics = From p In movie.Posters Select p.ImageInfo.Size, p.ImageInfo.Height, p.ImageInfo.Width

                                    'For Each p In pics
                                    '    Console.WriteLine("{0} - {1}x{2}", p.Size, p.Width, p.Height)
                                    'Next

                                    If _url IsNot Nothing Then
                                        Console.WriteLine("Caching posters for: {0}", movie.Name)
                                        'wc.DownloadFile(_url, My.Computer.FileSystem.CombinePath(baseDir, upc & ".jpg"))
                                    End If



                                    Dim movieImg = test.GetMovieImages(movie.Id)


                                    Dim posters = From u In movieImg.Posters Select u
                                    Try
                                        If posters IsNot Nothing Then
                                            For Each p In posters
                                                With p.ImageInfo
                                                    Dim i As New Tmdb_ImageInfo()
                                                    i.Height = .Height
                                                    i.ImageID = .Id
                                                    i.MovieID = t_Movie.Id
                                                    i.Size = .Size
                                                    i.Type = .Type
                                                    i.URL = .Url
                                                    i.Width = .Width
                                                    movlib.Tmdb_ImageInfos.InsertOnSubmit(i)

                                                End With
                                            Next
                                            movlib.SubmitChanges()

                                        End If
                                    Catch ex As Exception
                                        Red("Error in posters: " & ex.Message)
                                    End Try

                                    Dim backdrops = From b In movieImg.Backdrops

                                    Try
                                        If backdrops IsNot Nothing Then
                                            For Each b In backdrops
                                                With b.ImageInfo
                                                    Dim i As New Tmdb_ImageInfo()
                                                    i.Height = .Height
                                                    i.ImageID = .Id
                                                    i.MovieID = t_Movie.Id
                                                    i.Size = .Size
                                                    i.Type = .Type
                                                    i.URL = .Url
                                                    i.Width = .Width
                                                    movlib.Tmdb_ImageInfos.InsertOnSubmit(i)
                                                End With
                                            Next
                                            movlib.SubmitChanges()
                                        End If
                                    Catch ex As Exception
                                        Red("Error in backdrops: " & ex.Message)
                                    End Try
                                    'Dim actors = From c In movie.Cast Select c.Name


                                    'For Each a As String In actors
                                    '    Console.WriteLine(a)
                                    'Next
                                End If
                            Catch ex As Exception
                                Red(ex.Message)
                            End Try

                        End If


                    Catch ex As Exception
                        Red(ex.Message)
                    End Try

                End If
            End If
        Loop

    End Sub

    Private Sub DownloadPoster(ByVal UPC As String)

    End Sub
End Module
